import sqlite3 from 'sqlite3';
import path from 'path';
import { promisify } from 'util';
import fs from 'fs';
import { getDatabasePath } from './database-path';

const dbPath = getDatabasePath();

// 确保数据目录存在
const dataDir = path.dirname(dbPath);
if (!fs.existsSync(dataDir)) {
  fs.mkdirSync(dataDir, { recursive: true });
}

// 全局初始化标志，防止重复初始化
let globalInitialized = false;

class Database {
  private db: sqlite3.Database;

  constructor() {
    this.db = new sqlite3.Database(dbPath);
  }

  async initialize() {
    if (globalInitialized) return;

    const run = promisify(this.db.run.bind(this.db));
    
    try {
      // 创建用户表
      await run(`
        CREATE TABLE IF NOT EXISTS users (
          id TEXT PRIMARY KEY,
          username TEXT UNIQUE NOT NULL,
          email TEXT UNIQUE NOT NULL,
          password TEXT NOT NULL,
          role TEXT DEFAULT 'editor',
          name TEXT NOT NULL,
          avatar TEXT,
          is_active INTEGER DEFAULT 1,
          last_login_at TEXT,
          created_at TEXT DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // 创建成员表
      await run(`
        CREATE TABLE IF NOT EXISTS members (
          id TEXT PRIMARY KEY,
          name TEXT NOT NULL,
          role TEXT NOT NULL,
          title TEXT,
          email TEXT,
          phone TEXT,
          avatar TEXT,
          bio TEXT,
          research_areas TEXT,
          homepage TEXT,
          order_index INTEGER DEFAULT 0,
          is_active INTEGER DEFAULT 1,
          join_date TEXT,
          graduation_date TEXT,
          current_position TEXT,
          created_at TEXT DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // 创建项目表
      await run(`
        CREATE TABLE IF NOT EXISTS projects (
          id TEXT PRIMARY KEY,
          title TEXT NOT NULL,
          description TEXT NOT NULL,
          full_description TEXT,
          image TEXT,
          images TEXT,
          status TEXT DEFAULT 'ongoing',
          category TEXT NOT NULL,
          tags TEXT,
          start_date TEXT NOT NULL,
          end_date TEXT,
          members TEXT,
          achievements TEXT,
          links TEXT,
          order_index INTEGER DEFAULT 0,
          is_published INTEGER DEFAULT 1,
          created_at TEXT DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // 创建新闻表
      await run(`
        CREATE TABLE IF NOT EXISTS news (
          id TEXT PRIMARY KEY,
          title TEXT NOT NULL,
          summary TEXT NOT NULL,
          content TEXT NOT NULL,
          image TEXT,
          images TEXT,
          category TEXT DEFAULT 'news',
          tags TEXT,
          author TEXT NOT NULL,
          publish_date TEXT NOT NULL,
          is_published INTEGER DEFAULT 1,
          is_pinned INTEGER DEFAULT 0,
          view_count INTEGER DEFAULT 0,
          created_at TEXT DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
      `);
      
      // 添加images字段（如果不存在）
      try {
        await run(`ALTER TABLE news ADD COLUMN images TEXT`);
      } catch (error) {
        // 字段可能已存在，忽略错误
      }

      // 创建招新表
      await run(`
        CREATE TABLE IF NOT EXISTS recruitments (
          id TEXT PRIMARY KEY,
          title TEXT NOT NULL,
          description TEXT NOT NULL,
          requirements TEXT,
          responsibilities TEXT,
          positions INTEGER DEFAULT 1,
          deadline TEXT,
          contact_email TEXT NOT NULL,
          contact_phone TEXT,
          is_active INTEGER DEFAULT 1,
          category TEXT DEFAULT 'undergraduate',
          application_form TEXT,
          created_at TEXT DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // 创建申请表
      await run(`
        CREATE TABLE IF NOT EXISTS applications (
          id TEXT PRIMARY KEY,
          recruitment_id TEXT NOT NULL,
          applicant_name TEXT NOT NULL,
          applicant_email TEXT NOT NULL,
          applicant_phone TEXT,
          form_data TEXT NOT NULL,
          attachments TEXT,
          status TEXT DEFAULT 'pending',
          notes TEXT,
          reviewed_by TEXT,
          reviewed_at TEXT,
          created_at TEXT DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
          FOREIGN KEY (recruitment_id) REFERENCES recruitments (id)
        )
      `);

      // 创建媒体文件表
      await run(`
        CREATE TABLE IF NOT EXISTS media_files (
          id TEXT PRIMARY KEY,
          filename TEXT NOT NULL,
          original_name TEXT NOT NULL,
          path TEXT NOT NULL,
          url TEXT NOT NULL,
          mime_type TEXT NOT NULL,
          size INTEGER NOT NULL,
          alt TEXT,
          caption TEXT,
          category TEXT,
          uploaded_by TEXT,
          created_at TEXT DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // 创建联系方式表
      await run(`
        CREATE TABLE IF NOT EXISTS contacts (
          id TEXT PRIMARY KEY,
          type TEXT NOT NULL,
          title TEXT NOT NULL,
          description TEXT,
          content TEXT NOT NULL,
          qr_code TEXT,
          is_visible INTEGER DEFAULT 1,
          order_num INTEGER DEFAULT 1,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // 创建网站配置表
      await run(`
        CREATE TABLE IF NOT EXISTS site_config (
          id TEXT PRIMARY KEY DEFAULT 'main',
          title TEXT DEFAULT '机器人创新实验室',
          subtitle TEXT,
          description TEXT DEFAULT '专注于机器人技术研发与创新',
          logo TEXT,
          favicon TEXT,
          contact_info TEXT,
          theme TEXT,
          seo TEXT,
          navigation TEXT,
          footer TEXT,
          analytics TEXT,
          carousel_settings TEXT,
          updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // 添加轮播设置字段（如果不存在）
      try {
        await run(`ALTER TABLE site_config ADD COLUMN carousel_settings TEXT`);
      } catch (error) {
        // 字段可能已存在，忽略错误
      }

      // 不再自动插入任何默认数据
      // 只有通过 init-db.js 脚本才会创建初始数据

      globalInitialized = true;
      console.log('数据库初始化完成');
    } catch (error) {
      console.error('数据库初始化失败:', error);
      throw error;
    }
  }

  async run(sql: string, params: any[] = []): Promise<sqlite3.RunResult> {
    await this.initialize();
    return new Promise((resolve, reject) => {
      this.db.run(sql, params, function(err) {
        if (err) reject(err);
        else resolve(this);
      });
    });
  }

  async get<T = any>(sql: string, params: any[] = []): Promise<T | undefined> {
    await this.initialize();
    return new Promise<T | undefined>((resolve, reject) => {
      this.db.get(sql, params, (err, row) => {
        if (err) reject(err);
        else resolve(row as T | undefined);
      });
    });
  }

  async all<T = any>(sql: string, params: any[] = []): Promise<T[]> {
    await this.initialize();
    return new Promise<T[]>((resolve, reject) => {
      this.db.all(sql, params, (err, rows) => {
        if (err) reject(err);
        else resolve((rows || []) as T[]);
      });
    });
  }

  async close(): Promise<void> {
    return new Promise((resolve, reject) => {
      this.db.close((err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  }
}

export const db = new Database();